package com.john.excel.create;

import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;

import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

public class ExcelCreateSXSSF {

	private static Integer countHideSheet = 1;
	
	private static CellStyle style = null;
	
	/**
	 * 构建带下拉单元格xlsx并下载(支持下拉联动)
	 * @param response response
	 * @param selectList 下拉数据
	 * @param rows 总行数
	 * @param titles 标题数据
	 * @param values 内容数组
	 * @param title 标题对象
	 */
	public static void createDownloadXlsx(HttpServletResponse response, List<SelectCellAttr> selectList, Integer rows,
			List<CellAttr> titles, List<List<CellAttr>> values, CellAttr title) {
		try {
			String fileName = title.getVlaue() + ".xlsx";
			response.setHeader("Content-Type", "application/force-download");
			response.setHeader("Content-Type", "application/vnd.ms-excel");
			response.setHeader("Content-Disposition",
					"attachment; filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1"));// 设定输出文件头
			OutputStream out = response.getOutputStream();
			// 创建一个excel
			Workbook book = new SXSSFWorkbook(1000);
			style = book.createCellStyle();
			// 创建需要用户填写的sheet
			Sheet sheetPro =  book.createSheet(title.getVlaue());
			Integer startRowIndex = 2; // 从0开始
			sheetPro.setDefaultRowHeight((short) 360);
			sheetPro.setDefaultColumnWidth(20);
			Row spitrow = sheetPro.createRow(0);
			Cell spitcell = spitrow.createCell(0);
			spitcell.setCellValue(title.getVlaue());
			spitcell.setCellStyle(getTitleStyle(book, title));
			sheetPro.addMergedRegion(new CellRangeAddress(0, 1, 0, (titles.size() - 1))); // 合并单元格 开始行 结束行 开始列 结束列
			setDaeValidation(book, sheetPro, 0, 0, title);
			Row row = sheetPro.createRow(startRowIndex); // 设置标题
			for (int i = 0; i < titles.size(); i++) {
				CellAttr cellValue = titles.get(i);
				row.setHeight(cellValue.getRowHeight());
				Cell cell = row.createCell(i);
				cell.setCellValue(cellValue.getVlaue());
				cell.setCellStyle(getTitleStyle(book, cellValue));
				sheetPro.setColumnWidth(i,cellValue.getCellWith());
				setDaeValidation(book, sheetPro, startRowIndex, i, cellValue);
			}
			if (selectList != null) {
				// 创建专门用来存放数据信息的隐藏sheet页
				createHideSheet(book, selectList);
				// 设置效验
				dataValidation(book, sheetPro, selectList, rows);
			}
			if (values != null) {
				int rows1 = 3; // 行号
				for (int i = 0; i < values.size(); i++) {
					Row row1 = sheetPro.createRow(rows1++); // 创建数据行
					List<CellAttr> cell = values.get(i);
					for (int j = 0; j < cell.size(); j++) {
						CellAttr cellValue = cell.get(j);
						row1.setHeight(cellValue.getRowHeight());
						Cell cell1 = row1.createCell(j);
						cell1.setCellValue(cellValue.getVlaue());
						if(cellValue.isSetStyle()){
							cell1.setCellStyle(getTitleStyle(book, cellValue));
						}
						if(cellValue.isSetPromptType()){
							setDaeValidation(book, sheetPro, rows1, j, cellValue);
						}
						sheetPro.setColumnWidth(j,cellValue.getCellWith());
					}
				}
			}
			book.write(out);
			out.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 构建xlsx并下载
	 * @param response response
	 * @param cells 总列数
	 * @param title 标题对象
	 * @param rowData 内容数组
	 */
	public static void createDownloadXlsx(HttpServletResponse response,Integer cells, CellAttr title, List<List<CellAttr>> rowData) {
		try {
			String fileName = title.getVlaue() + ".xlsx";
			response.setHeader("Content-Type", "application/force-download");
			response.setHeader("Content-Type", "application/vnd.ms-excel");
			response.setHeader("Content-Disposition",
					"attachment; filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1"));// 设定输出文件头
			OutputStream out = response.getOutputStream();
			// 创建一个excel
			Workbook book = new SXSSFWorkbook(1000);
			style = book.createCellStyle();
			// 创建需要用户填写的sheet
			Sheet sheetPro = book.createSheet(title.getVlaue());
			Integer startRowIndex = 2; // 从0开始
			sheetPro.setDefaultRowHeight((short) 360);
			sheetPro.setDefaultColumnWidth(20);
			Row spitrow = sheetPro.createRow(0);
			Cell spitcell = spitrow.createCell(0);
			spitcell.setCellValue(title.getVlaue());
			spitcell.setCellStyle(getTitleStyle(book, title));
			sheetPro.addMergedRegion(new CellRangeAddress(0, 1, 0, cells)); // 合并单元格 开始行 结束行 开始列 结束列
			setDaeValidation(book, sheetPro, 0, 0, title);
			if (rowData != null) {
				for (int i = 0; i < rowData.size(); i++) {
					Row row1 = sheetPro.createRow(startRowIndex); // 创建数据行
					List<CellAttr> cell = rowData.get(i);
					for (int j = 0; j < cell.size(); j++) {
						CellAttr cellValue = cell.get(j);
						row1.setHeight(cellValue.getRowHeight());
						Cell cell1 = row1.createCell(j);
						cell1.setCellValue(cellValue.getVlaue());
						if(cellValue.isSetStyle()){
							cell1.setCellStyle(getTitleStyle(book, cellValue));
						}
						if(cellValue.isSetPromptType()){
							setDaeValidation(book, sheetPro, startRowIndex, j, cellValue);;
						}
						sheetPro.setColumnWidth(j,cellValue.getCellWith());
					}
					if(cell.size() > 0){
						for (int j = cell.size(); j <= cells; j++) {
							CellAttr cellValue = cell.get(cell.size() - 1);
							row1.setHeight(cellValue.getRowHeight());
							Cell cell1 = row1.createCell(j);
							cellValue.setBackgroundColor("ffffff");
							if(cellValue.isSetStyle()){
								cell1.setCellStyle(getTitleStyle(book, cellValue));
							}
							if(cellValue.isSetPromptType()){
								setDaeValidation(book, sheetPro, startRowIndex, j, cellValue);
							}
							sheetPro.setColumnWidth(j,cellValue.getCellWith());
						}
					}
					startRowIndex++;
				}
			}
			book.write(out);
			out.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 构建xlsx并保持到本地
	 * @param cells 总列数
	 * @param title 标题对象
	 * @param filePath 文件保存位置
	 * @param rowData 内容数组
	 */
	public static void createXlsxRows(Integer cells, CellAttr title, String filePath, List<List<CellAttr>> rowData) {
		FileOutputStream os = null;
		cells--;
		try {
			// 创建一个excel
			Workbook book = new SXSSFWorkbook(1000); //重点在这句 .每1000条写一个临时文件
			style = book.createCellStyle();
			// 创建需要用户填写的sheet
			Sheet sheetPro = book.createSheet(title.getVlaue());
			Integer startRowIndex = 1; // 从0开始
			sheetPro.setDefaultRowHeight((short) 360);
			sheetPro.setDefaultColumnWidth(20);
			sheetPro.addMergedRegion(new CellRangeAddress(0, 0, 0, cells)); // 合并单元格 开始行 结束行 开始列 结束列
			Row spitrow = sheetPro.createRow(0);
			Cell spitcell = spitrow.createCell(0);
			spitcell.setCellValue(title.getVlaue());
			spitcell.setCellStyle(getTitleStyle(book, title));
			spitrow.setHeight(title.getRowHeight());
			setDaeValidation(book, sheetPro, 0, 0, title);
			filePath += "\\" +title.getVlaue() + ".xlsx";
			if (rowData != null) {
				for (int i = 0; i < rowData.size(); i++) {
					Row row1 = sheetPro.createRow(startRowIndex); // 创建数据行
					List<CellAttr> cell = rowData.get(i);
					for (int j = 0; j < cell.size(); j++) {
						CellAttr cellValue = cell.get(j);
						row1.setHeight(cellValue.getRowHeight());
						Cell cell1 = row1.createCell(j);
						cell1.setCellValue(cellValue.getVlaue());
						if(cellValue.isSetStyle()){
							cell1.setCellStyle(getTitleStyle(book, cellValue));
						}
						if(cellValue.isSetPromptType()){
							setDaeValidation(book, sheetPro, (startRowIndex + 1), j, cellValue);
						}
						sheetPro.setColumnWidth(j,cellValue.getCellWith());
					}
					startRowIndex++;
					if(i%1000==0){
						System.out.println(i);
					}
				}
			}
			filePath = filePath.replace("\\", "/");
			os = new FileOutputStream(filePath);
			book.write(os);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			IOUtils.closeQuietly(os);
		}
	}

	/**
	 * 构建带下拉单元格xlsx,并保持到本地(支持下拉联动)
	 * @param selectList 下拉数据数组
	 * @param rows 总行数
	 * @param titles 标题数组
	 * @param values 内容数组
	 * @param title 标题对象
	 * @param filePath 保存位置
	 */
	public static void createXlsx(List<SelectCellAttr> selectList, Integer rows, List<CellAttr> titles,List<List<CellAttr>> values, CellAttr title, String filePath) {
		FileOutputStream os = null;
		try {
			// 创建一个excel
			Workbook book = new SXSSFWorkbook(1000);
			style = book.createCellStyle();
			// 创建需要用户填写的sheet
			Sheet sheetPro = book.createSheet(title.getVlaue());
			Integer startRowIndex = 2; // 从0开始
			sheetPro.setDefaultRowHeight((short) 360);
			sheetPro.setDefaultColumnWidth(20);
			Row spitrow = sheetPro.createRow(0);
			Cell spitcell = spitrow.createCell(0);
			spitcell.setCellValue(title.getVlaue());
			spitcell.setCellStyle(getTitleStyle(book, title));
			spitrow.setHeight(title.getRowHeight());
			sheetPro.addMergedRegion(new CellRangeAddress(0, 1, 0, (titles.size() - 1))); // 合并单元格 开始行 结束行 开始列 结束列
			setDaeValidation(book, sheetPro, 0, 0, title);
			Row row = sheetPro.createRow(startRowIndex); // 设置标题
			filePath += "\\" +title.getVlaue() + ".xlsx";
			for (int i = 0; i < titles.size(); i++) {
				CellAttr cellValue = titles.get(i);
				row.setHeight(cellValue.getRowHeight());
				Cell cell = row.createCell(i);
				cell.setCellValue(cellValue.getVlaue());
				cell.setCellStyle(getTitleStyle(book, cellValue));
				sheetPro.setColumnWidth(i,cellValue.getCellWith());
				setDaeValidation(book, sheetPro, startRowIndex, i, cellValue);
			}
			if (selectList != null) {
				// 创建专门用来存放数据信息的隐藏sheet页
				createHideSheet(book, selectList);
				// 设置效验
				dataValidation(book, sheetPro, selectList, rows);
			}
			if (values != null) {
				int rows1 = 3; // 行号
				for (int i = 0; i < values.size(); i++) {
					Row row1 = sheetPro.createRow(rows1++); // 创建数据行
					List<CellAttr> cell = values.get(i);
					for (int j = 0; j < cell.size(); j++) {
						CellAttr cellValue = cell.get(j);
						row1.setHeight(cellValue.getRowHeight());
						Cell cell1 = row1.createCell(j);
						cell1.setCellValue(cellValue.getVlaue());
						if(cellValue.isSetStyle()){
							cell1.setCellStyle(getTitleStyle(book, cellValue));
						}
						if(cellValue.isSetPromptType()){
							setDaeValidation(book, sheetPro, rows1, j, cellValue);
						}
						sheetPro.setColumnWidth(j,cellValue.getCellWith());
					}
				}
			}
			os = new FileOutputStream(filePath);
			book.write(os);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			IOUtils.closeQuietly(os);
		}
	}

	/**
	 * 根据颜色值设置颜色
	 * @param color #ffffff or ffffff
	 * @return
	 */
	private static short getColor(String color) {
		if(color == null){
			System.out.println(color);
		}
		color = color.indexOf("#") == -1 ? "#" + color : color;
		HSSFPalette palette = new HSSFWorkbook().getCustomPalette();
		HSSFColor hssFColor = palette.findColor((byte) Integer.parseInt(color.substring(1, 3), 16),
				(byte) Integer.parseInt(color.substring(3, 5), 16), (byte) Integer.parseInt(color.substring(5, 7), 16));
		if (hssFColor == null) {
			hssFColor = palette.findSimilarColor((byte) Integer.parseInt(color.substring(1, 3), 16),
					(byte) Integer.parseInt(color.substring(3, 5), 16),
					(byte) Integer.parseInt(color.substring(5, 7), 16));
			if (hssFColor == null) {
				hssFColor = palette.addColor((byte) Integer.parseInt(color.substring(1, 3), 16),
						(byte) Integer.parseInt(color.substring(3, 5), 16),
						(byte) Integer.parseInt(color.substring(5, 7), 16));
			}
		}
		return hssFColor.getIndex();
	}

	/**
	 * 添加隐藏数据页
	 * @param book 工作簿
	 * @param sheetPro 操作sheet页
	 * @param selectList 下拉数组
	 * @param rows 总行数
	 */
	private static void createHideSheet(Workbook book, List<SelectCellAttr> selectList) {
		try {
			// 创建一个专门用来存放地区信息的隐藏sheet页
			// 因此也不能在现实页之前创建，否则无法隐藏。
			Sheet hideSheet = null;
			// 获取非级联下拉
			List<SelectCellAttr> selectList1 = new ArrayList<SelectCellAttr>();
			List<SelectCellAttr> selectList2 = new ArrayList<SelectCellAttr>();
			for (int i = 0; i < selectList.size(); i++) {
				SelectCellAttr cellAttr = selectList.get(i);
				if (!cellAttr.isCascade() && cellAttr.getData() != null) { // 如果是非级联
					selectList1.add(cellAttr);
				}
				if (cellAttr.getData() != null) {
					selectList2.add(cellAttr);
				}
			}
			for (int b = 0; b < selectList1.size(); b++) {
				SelectCellAttr cellAttr = selectList1.get(b);
				if (!cellAttr.isCascade()) { // 如果不是级联则创建一个新的数据hideSheet
					hideSheet = book.createSheet("area" + countHideSheet);
					// 这一行作用是将此sheet隐藏，功能未完成时注释此行,可以查看隐藏sheet中信息是否正确
					book.setSheetHidden(book.getSheetIndex(hideSheet), true);
					int rowId = 0;
					boolean isCascade = false;
					// 设置关联数据
					for (int i = 0; i < selectList2.size(); i++) {
						cellAttr = selectList2.get(i);
						if (!cellAttr.isCascade() && isCascade) {
							countHideSheet++;
							break;
						}
						String[] data = cellAttr.getData();
						Row provinceRow = hideSheet.createRow(rowId++);
						provinceRow.createCell(0).setCellValue(cellAttr.getName());
						for (int j = 0; j < data.length; j++) {
							Cell provinceCell = provinceRow.createCell(j + 1);
							provinceCell.setCellValue(data[j]);
						}
						if (cellAttr.isCascade()) {
							setRefersToFormula(cellAttr.getName(), rowId, data, book);
							selectList2.remove(i);
							i--;
						}
						if (!cellAttr.isCascade() && !isCascade) {
							isCascade = true;
							selectList2.remove(i);
							i--;
						}
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 数据效验,提示等
	 * @param book 工作簿
	 * @param sheetPro 当前页sheet
	 * @param selectList
	 * @param rows 总行数
	 */
	private static void dataValidation(Workbook book, Sheet sheetPro, List<SelectCellAttr> selectList,
			Integer rows) {
		for (int j = 0; j < selectList.size(); j++) {
			SelectCellAttr cellAttr = selectList.get(j);
			sheetPro.setDefaultColumnStyle(cellAttr.getCellIndex(), getTitleStyle(book, cellAttr));
		}
		// 对前20行设置有效性
		for (int i = 4; i <= rows; i++) {
			// 设置数据有效性提示等
			for (int j = 0; j < selectList.size(); j++) {
				SelectCellAttr cellAttr = selectList.get(j);
				String[] data = cellAttr.getData();
				if (cellAttr.isCascade()) {
					setDataValidation(getcellColumnFlag(cellAttr.getLevel()), sheetPro, i, cellAttr);
				} else {
					// 普通下拉验证
					setDataValidation(sheetPro, data, i, cellAttr);
				}
			}
		}
	}
	
	/**
	 * 设置模板文件的横向表头单元格的样式
	 * @param wb
	 * @param cellValue
	 * @return
	 */
	private static CellStyle getTitleStyle(Workbook wb, CellAttr cellValue) {
		style = wb.createCellStyle();
		// 对齐方式设置
		style.setVerticalAlignment(cellValue.getVerticalAlignment());// 垂直
		style.setAlignment(cellValue.getAlignment());// 水平
		style.setWrapText(cellValue.getWrapText());// 设置自动换行
		// 边框颜色和宽度设置
		style.setBorderBottom(cellValue.getBorder());
		style.setBottomBorderColor(getColor(cellValue.getBorderColor()));
		style.setBorderLeft(cellValue.getBorder());
		style.setLeftBorderColor(getColor(cellValue.getBorderColor()));
		style.setBorderRight(cellValue.getBorder());
		style.setRightBorderColor(getColor(cellValue.getBorderColor()));
		style.setBorderTop(cellValue.getBorder());
		style.setTopBorderColor(getColor(cellValue.getBorderColor()));
		style.setFillBackgroundColor(getColor(cellValue.getbackgroundColor()));
		// 设置背景颜色
		style.setFillForegroundColor(getColor(cellValue.getbackgroundColor()));
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		// 粗体字设置
		Font font = wb.createFont();
		font.setBoldweight(Font.BOLDWEIGHT_BOLD);
		font.setFontHeightInPoints((short) 10); // 字体高度
		font.setColor(getColor(cellValue.getFontColor()));
		style.setFont(font);
		return style;
	}
	
	/**
	 * 设置模板文件的横向表头单元格的样式
	 * @param wb
	 * @param cellValue
	 * @return
	 */
	private static CellStyle getTitleStyle(Workbook wb, SelectCellAttr cellValue) {
		// 对齐方式设置
		style.setVerticalAlignment(cellValue.getVerticalAlignment());// 垂直
		style.setAlignment(cellValue.getAlignment());// 水平
		style.setWrapText(cellValue.getWrapText());// 设置自动换行
		// 边框颜色和宽度设置
		style.setBorderBottom(cellValue.getBorder());
		style.setBottomBorderColor(getColor(cellValue.getBorderColor()));
		style.setBorderLeft(cellValue.getBorder());
		style.setLeftBorderColor(getColor(cellValue.getBorderColor()));
		style.setBorderRight(cellValue.getBorder());
		style.setRightBorderColor(getColor(cellValue.getBorderColor()));
		style.setBorderTop(cellValue.getBorder());
		style.setTopBorderColor(getColor(cellValue.getBorderColor()));
		if(cellValue.getbackgroundColor() == null){
			System.out.println(cellValue.getbackgroundColor());
		}
		style.setFillBackgroundColor(getColor(cellValue.getbackgroundColor()));
		// 设置背景颜色
		style.setFillForegroundColor(getColor(cellValue.getbackgroundColor()));
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		// 粗体字设置
		Font font = wb.createFont();
		font.setBoldweight(Font.BOLDWEIGHT_BOLD);
		font.setFontHeightInPoints((short) 10); // 字体高度
		font.setColor(getColor(cellValue.getFontColor()));
		style.setFont(font);
		return style;
	}

	/**
	 * 设置下拉数据关联
	 * @param key 上级关联内容,不可重复
	 * @param rowId 行数
	 * @param data 内容数组
	 * @param book 工作簿
	 */
	private static void setRefersToFormula(String key, Integer rowId, String[] data, Workbook book) {
		// 添加名称管理器
		String range = getRange(1, rowId, data.length);
		Name name = book.createName();
		// key不可重复
		name.setNameName(key);
		String formula = "area" + countHideSheet + "!" + range;
		name.setRefersToFormula(formula);
	}

	/**
	 * 根据数据值确定单元格位置（比如：28-AB）
	 * @param num
	 * @return
	 */
	private static String getcellColumnFlag(int num) {
		String columFiled = "";
		int chuNum = 0;
		int yuNum = 0;
		if (num >= 1 && num <= 26) {
			columFiled = doHandle(num);
		} else {
			chuNum = num / 26;
			yuNum = num % 26;

			columFiled += doHandle(chuNum);
			columFiled += doHandle(yuNum);
		}
		return columFiled;
	}

	/**
	 * 获取xlsx列标识
	 * @param num
	 * @return
	 */
	private static String doHandle(final int num) {
		String[] charArr = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R",
				"S", "T", "U", "V", "W", "X", "Y", "Z" };
		return charArr[num - 1].toString();
	}

	/**
	 * 设置有效性
	 * @param offset 主影响单元格所在列，即此单元格由哪个单元格影响联动
	 * @param sheet
	 * @param rowNum 行数
	 * @param cellAttr 列数
	 */
	private static void setDataValidation(String offset, Sheet sheet, int rowNum, SelectCellAttr cellAttr) {
		DataValidationHelper dvHelper = sheet.getDataValidationHelper();
		DataValidation data_validation_list;
		data_validation_list = getDataValidationByFormula("INDIRECT($" + offset + (rowNum) + ")", rowNum, cellAttr,dvHelper);
		sheet.addValidationData(data_validation_list);
	}
	
	/**
	 * 设置普通单元格提示
	 * @param cellValue
	 */
	private static void setDaeValidation(Workbook book, Sheet sheet, int rowNum, int cellindex,CellAttr cellValue){
		try {
			if(cellValue.getPromptType() != null){
				setDataValidationByDate(book, sheet, rowNum, cellindex, cellValue);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 设置单元格提示
	 * @param sheet
	 * @param rowNum 行数
	 * @param colNum 列数
	 */
	private static void setDataValidationByDate(Workbook wb, Sheet sheet, int rowNum, SelectCellAttr cellAttr) {
		DataValidationHelper dvHelper = sheet.getDataValidationHelper();
		// 日期效验
		DataValidationConstraint provConstraint = dvHelper.createDateConstraint(
				DataValidationConstraint.OperatorType.NOT_BETWEEN, "1900-01-01", "5000-01-01", "yyyy-MM-dd");
		// 四个参数分别是：起始行、终止行、起始列、终止列
		int firstRow = rowNum - 1;
		int lastRow = rowNum - 1;
		int firstCol = cellAttr.getCellIndex() - 1;
		int lastCol = cellAttr.getCellIndex() - 1;
		CellRangeAddressList provRangeAddressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
		DataValidation provinceDataValidation = dvHelper.createValidation(provConstraint, provRangeAddressList);
		// 验证
		provinceDataValidation.setShowPromptBox(true);
		// 设置输入信息提示信息
		provinceDataValidation.createPromptBox(cellAttr.getPromptType().getKey(), cellAttr.getPromptType().getValue());
		sheet.addValidationData(provinceDataValidation);
	}
	
	/**
	 * 设置单元格提示
	 * @param sheet
	 * @param rowNum 行数
	 * @param colNum 列数
	 */
	private static void setDataValidationByDate(Workbook wb, Sheet sheet, int rowNum, int cellindex, CellAttr cellvalue) {
		DataValidationHelper dvHelper = sheet.getDataValidationHelper();
		// 日期效验
		DataValidationConstraint provConstraint = dvHelper.createDateConstraint(
				DataValidationConstraint.OperatorType.NOT_BETWEEN, "1900-01-01", "5000-01-01", "yyyy-MM-dd");
		// 四个参数分别是：起始行、终止行、起始列、终止列
		int firstRow = rowNum - 1;
		int lastRow = rowNum - 1;
		int firstCol = cellindex;
		int lastCol = cellindex;
		CellRangeAddressList provRangeAddressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
		DataValidation provinceDataValidation = dvHelper.createValidation(provConstraint, provRangeAddressList);
		// 验证
		provinceDataValidation.setShowPromptBox(true);
		// 设置输入信息提示信息
		provinceDataValidation.createPromptBox(cellvalue.getPromptType().getKey(), cellvalue.getPromptType().getValue());
		sheet.addValidationData(provinceDataValidation);
	}

	/**
	 * 设置有效性 非级联下拉有效性
	 * @param sheet
	 * @param rowNum 行数
	 * @param colNum 列数
	 */
	private static void setDataValidation(Sheet sheet, String[] data, int rowNum,SelectCellAttr cellAttr) {
		DataValidationHelper dvHelper = sheet.getDataValidationHelper();
		// 省规则
		DataValidationConstraint provConstraint = dvHelper.createExplicitListConstraint(data);
		// 四个参数分别是：起始行、终止行、起始列、终止列
		int firstRow = rowNum - 1;
		int lastRow = rowNum - 1;
		int firstCol = cellAttr.getCellIndex() - 1;
		int lastCol = cellAttr.getCellIndex() - 1;
		CellRangeAddressList provRangeAddressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
		DataValidation provinceDataValidation = dvHelper.createValidation(provConstraint, provRangeAddressList);
		// 验证
		provinceDataValidation.setSuppressDropDownArrow(true);
		provinceDataValidation.setShowErrorBox(true);
		provinceDataValidation.setShowPromptBox(true);
		if(cellAttr.getPromptType() == null){
			cellAttr.setPromptType(new PromptBox("选择提示", "请下拉选择合适的值!"));
			cellAttr.getPromptType().setErr("错误提示", "你输入的值未在备选列表中，请下拉选择合适的值!");
		}else if(cellAttr.getPromptType() != null && cellAttr.getPromptType().getKey() == null){
			cellAttr.setPromptType(new PromptBox("选择提示", "请下拉选择合适的值!"));
		}else if(cellAttr.getPromptType() != null && cellAttr.getPromptType().getKey_err() == null){
			cellAttr.getPromptType().setErr("错误提示", "你输入的值未在备选列表中，请下拉选择合适的值!");
		}
		// 设置输入信息提示信息
		provinceDataValidation.createPromptBox(cellAttr.getPromptType().getKey(),cellAttr.getPromptType().getValue());
		// 设置输入错误提示信息
		provinceDataValidation.createErrorBox(cellAttr.getPromptType().getKey_err(), cellAttr.getPromptType().getValue_err());
		sheet.addValidationData(provinceDataValidation);
	}

	/**
	 * 加载下拉列表内容
	 * @param formulaString
	 * @param naturalRowIndex
	 * @param cellAttr
	 * @param dvHelper
	 * @return
	 */
	private static DataValidation getDataValidationByFormula(String formulaString, int naturalRowIndex,
			SelectCellAttr cellAttr, DataValidationHelper dvHelper) {
		// 举例：若formulaString = "INDIRECT($A$2)" 表示规则数据会从名称管理器中获取key与单元格 A2
		// 值相同的数据，
		// 如果A2是江苏省，那么此处就是江苏省下的市信息。
		XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
				.createFormulaListConstraint(formulaString);
		// 设置数据有效性加载在哪个单元格上。
		// 四个参数分别是：起始行、终止行、起始列、终止列
		int firstRow = naturalRowIndex - 1;
		int lastRow = naturalRowIndex - 1;
		int firstCol = cellAttr.getCellIndex() - 1;
		int lastCol = cellAttr.getCellIndex() - 1;
		CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
		// 数据有效性对象
		// 绑定
		XSSFDataValidation data_validation_list = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
		data_validation_list.setEmptyCellAllowed(false);
		data_validation_list.setSuppressDropDownArrow(true);
		data_validation_list.setShowErrorBox(true);
		data_validation_list.setShowPromptBox(true);
		if(cellAttr.getPromptType() == null){
			cellAttr.setPromptType(new PromptBox("选择提示", "请下拉选择合适的值!"));
			cellAttr.getPromptType().setErr("错误提示", "你输入的值未在备选列表中，请下拉选择合适的值!");
		}
		// 设置输入信息提示信息
		data_validation_list.createPromptBox(cellAttr.getPromptType().getKey(), cellAttr.getPromptType().getValue());
		// 设置输入错误提示信息
		data_validation_list.createErrorBox(cellAttr.getPromptType().getKey_err(), cellAttr.getPromptType().getValue_err());
		return data_validation_list;
	}

	/**
	 * 计算formula
	 * @param offset 偏移量，如果给0，表示从A列开始，1，就是从B列
	 * @param rowId 第几行
	 * @param colCount 一共多少列
	 * @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
	 * 
	 */
	private static String getRange(int offset, int rowId, int colCount) {
		char start = (char) ('A' + offset);
		if (colCount <= 25) {
			char end = (char) (start + colCount - 1);
			return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;
		} else {
			char endPrefix = 'A';
			char endSuffix = 'A';
			if ((colCount - 25) / 26 == 0 || colCount == 51) {// 26-51之间，包括边界（仅两次字母表计算）
				if ((colCount - 25) % 26 == 0) {// 边界值
					endSuffix = (char) ('A' + 25);
				} else {
					endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
				}
			} else {// 51以上
				if ((colCount - 25) % 26 == 0) {
					endSuffix = (char) ('A' + 25);
					endPrefix = (char) (endPrefix + (colCount - 25) / 26 - 1);
				} else {
					endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
					endPrefix = (char) (endPrefix + (colCount - 25) / 26);
				}
			}
			return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;
		}
	}

}